﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using ZLDC.Common.Estate;
using ZLDC.Topo.Model.Estate;
using System.Configuration;

namespace ZLDC.DAL.Estate
{
    public class lp_Basis
    {
        public static string sqlconnection = ConfigurationManager.AppSettings["estate"];
        public static int SqlQuery(string sql,_Database db)
        {
            int rows = 0;
            rows = db.ExecuteSql(sql);
            db.close();
            return rows;
        }

        public static DataSet GetTb(string sql,_Database db)
        {
            return db.Query(sql);
        }
        public static int Execution(string sql, _Database db)
        {
            return db.ExecuteSql(sql);
        }
                                                        public static string Exists_Set(string sql, _Database db)
        {
            DataSet ds = db.Query(sql);
            if (ds!=null && ds.Tables[0].Rows.Count>0)
                return ds.Tables[0].Rows[0][0].ToString();
            else
                return "0";
        }
        
                                                                                public static DataTable lp_list(string tb, string col, string colorder, int orderby, string collist, int pagesize, int page, string condition,  ref int pages,ref int intResult )
        {
            SqlParameter[] parameters = { 
            new SqlParameter("@tb", SqlDbType.VarChar,50),
             new SqlParameter("@col", SqlDbType.VarChar,50),
              new SqlParameter("@colorder", SqlDbType.VarChar,50),
               new SqlParameter("@orderby", SqlDbType.Int,4),
                new SqlParameter("@collist", SqlDbType.VarChar,800),
                 new SqlParameter("@pagesize", SqlDbType.Int,4),
                  new SqlParameter("@page", SqlDbType.Int,4),
                   new SqlParameter("@condition", SqlDbType.VarChar,2800),
                    new SqlParameter("@pages",SqlDbType.Int,4),
                    new SqlParameter("@intResult",SqlDbType.Int,4)
                                        };
            parameters[0].Value = tb;
            parameters[1].Value = col;
            parameters[2].Value = colorder;
            parameters[3].Value = orderby;
            parameters[4].Value = collist;
            parameters[5].Value = pagesize;
            parameters[6].Value = page;
            parameters[7].Value = condition;
            parameters[8].Direction = ParameterDirection.InputOutput;
            parameters[9].Direction = ParameterDirection.InputOutput;
            DataSet dt = null;
            dt = RunProcedure("LP_ProPageSet1", parameters,"ds");
            
            pages = int.Parse(parameters[8].Value.ToString());
            intResult = int.Parse(parameters[9].Value.ToString());
            return dt.Tables[0];
        }

                                                                        public static int lp_pro_updatelist(string type, string userid, string deptid, string datetime)
        {
            int rowsAffected;
            SqlParameter[] parameters = { 
            new SqlParameter("@type", SqlDbType.VarChar,50),
             new SqlParameter("@userid", SqlDbType.VarChar,50),
              new SqlParameter("@deptid", SqlDbType.VarChar,50)
             
               
                                        };
            parameters[0].Value = type;
            parameters[1].Value = userid;
            parameters[2].Value = deptid;


            RunProcedure("LP_lp_Report_Userlist", parameters, out rowsAffected);
            return rowsAffected;
        }




                                                                        public static int lp_Configuration_main_add(string sql)
        {
            object obj = GetSingle(sql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

                                public static bool Exists(string sql)
        {
            return Exists_sql(sql);
        }

        public static bool Exists_sql(string strSql)
        {
            object obj = GetSingle(strSql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        #region sql执行语句 后期维护

        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (SqlConnection connection = new SqlConnection(sqlconnection))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }
                                                                public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            using (SqlConnection connection = new SqlConnection(sqlconnection))
            {
                int result;
                connection.Open();
                SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
                rowsAffected = command.ExecuteNonQuery();
                result = (int)command.Parameters["ReturnValue"].Value;
                connection.Close();
                return result;
            }
        }
                                                        private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add(new SqlParameter("ReturnValue",
                SqlDbType.Int, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }
                                                                private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }

            return command;
        }

                                                public static object GetSingle(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(sqlconnection))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }
        #endregion
    }
}
